﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sync.Service
{
    public class ExcelHelper
    {
        public static DataSet ExcelToDS(string Path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select * from [sheet1$] where Status = 0";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds, "table1");
            return ds;
        }

        public static void Update(string groupMemberId ,string buildingMemberId,string status,string Path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            var sql = $"update [sheet1$] set [G] = '{status}' where [D]='{groupMemberId}' and [B]='{buildingMemberId}'";
            //var sql = $"update [sheet1$] set status=? where d=? and b=?";
            sql = $"update [sheet1$] set [Status] = '{status}' where [GroupMemberId]= '{groupMemberId}' and [BuildingMemberId]= '{buildingMemberId}'";
            sql = $"UPDATE [sheet1$] set [Status] = ? WHERE [GroupMemberId]= ? and [BuildingMemberId]= ?";
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(new List<OleDbParameter>() {
                new OleDbParameter() { ParameterName="@status", Value = status },
                new OleDbParameter() { ParameterName="@groupMemberId", Value = groupMemberId },
                new OleDbParameter() { ParameterName="@buildingMemberId", Value = buildingMemberId }
                }.ToArray());
                var i = cmd.ExecuteNonQuery();

            }

        }

        public static void UpdateFileStatus(string fileId, string status, string Path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            var sql = "";
            //var sql = $"update [sheet1$] set status=? where d=? and b=?";
            sql = $"UPDATE [sheet1$] set [status] = ? WHERE [Id]= ? ";
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(new List<OleDbParameter>() {
                new OleDbParameter() { ParameterName="@status", Value = status },
                new OleDbParameter() { ParameterName="@fileId", Value = fileId },
                }.ToArray());
                var i = cmd.ExecuteNonQuery();

            }

        }
    }
}
